package com.kis.excel;

import com.kis.ExcelObject;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * Created by yks on 15-11-27.
 */
public class ReadExcel {
    public static List<ExcelObject> xls2String(File file) {

        List<ExcelObject> excelObjects = new LinkedList<ExcelObject>();
        try {
            FileInputStream fis = new FileInputStream(file);
            jxl.Workbook rwb = Workbook.getWorkbook(fis);
            Sheet sheet1 = rwb.getSheet(0);
            String head = null;
            String method = null;
            String code = null;
            for (int i = 4; i < 35; i++) {
                String tempHead = sheet1.getRow(i)[2].getContents();
                String tempMethod = sheet1.getRow(i)[3].getContents();
                String tempCode = sheet1.getRow(i)[5].getContents();
                if (StringUtils.isNotBlank(tempHead)) head = tempHead;
                if (StringUtils.isNotBlank(tempMethod)) method = tempMethod;
                if (StringUtils.isNotBlank(tempCode)) code = tempCode;
                ExcelObject excelObject = new ExcelObject(head, method, code);
                excelObjects.add(excelObject);
//                if(code.indexOf(",")>-1) {
//                    String[] codes = code.split(",");
//                    for (String s : codes) {
//                        ExcelObject excelObject = new ExcelObject(head,method,s);
//                        excelObjects.add(excelObject);
//                    }
//                }
            }
            fis.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return excelObjects;
    }

    public static void writeExcel(List<ExcelObject> excelObjects, String path) {
        Map<String, String> channelMap = ReadExcelChannel.xls2String(new File("/home/yks/channel.xls"));
        Map<String, String> countryMap = ReadExcelCountry.xls2String(new File("/home/yks/country.xls"));
        WritableWorkbook wwb = null;
        try {
            wwb = Workbook.createWorkbook(new File(path));
            WritableSheet wSheet = wwb.createSheet("sheet1", 0);
            for (int i = 0; i < excelObjects.size(); i++) {
                String first = excelObjects.get(i).getHead() + excelObjects.get(i).getMethod();
                if (StringUtils.isNotBlank(channelMap.get(excelObjects.get(i).getHead() + excelObjects.get(i).getMethod()))) {
                    first = channelMap.get(excelObjects.get(i).getHead() + excelObjects.get(i).getMethod());
                }
                String label2Text = null;
                String country = excelObjects.get(i).getCode();
                if (country.indexOf(",") > -1) {
                    StringBuffer sb = new StringBuffer();
                    String[] countryNames = country.split(",");
                    for (String countryName : countryNames) {
                        if (StringUtils.isNotBlank(countryMap.get(countryName))) {
                            sb.append(countryMap.get(countryName));
                        } else {
                            sb.append(countryName);
                        }
                        sb.append(",");
                    }
                    label2Text = sb.toString();
                } else {
                    label2Text = excelObjects.get(i).getCode();
                }

                Label label1 = new Label(1, i, first);
                Label label2 = new Label(2, i, label2Text);
//                Label label1 = new Label(1, i, excelObjects.get(i).getHead() + excelObjects.get(i).getMethod());
//                Label label2 = new Label(2, i, excelObjects.get(i).getCode());
                wSheet.addCell(label1);
                wSheet.addCell(label2);
//                wSheet.addCell(label3);
//                wSheet.addCell(label4);
            }
            wwb.write();
            wwb.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        File file = new File("/home/yks/t.xls");
        List<ExcelObject> excelObjects = xls2String(file);
        for (ExcelObject excelObject : excelObjects) {
            System.out.println(excelObject.getCode());
        }
        writeExcel(excelObjects, "/home/yks/w1.xls");
    }
}

